Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports
Imports CrystalDecisions
Imports CrystalDecisions.Reporting
Imports CrystalDecisions.CrystalReports.Engine



Partial Class Reports
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        ' If Not IsPostBack Then
        If Request.QueryString("rpt") <> "" Then
            pnlReportLinks.Visible = False
            If Request.QueryString("rpt").ToLower() = "sum" Then
                ShowYearlySummary()
            ElseIf Request.QueryString("rpt").ToLower() = "mon" Then
                ShowMonthlySummary()
            Else
                ShowPaymentVoucher()
            End If

        Else
            pnlReportLinks.Visible = True
            lnkPrintPayment.NavigateUrl = "ShowVouchers.aspx?mode=p"
            lnkPrintReciepts.NavigateUrl = "ShowVouchers.aspx?mode=r"
            lnkAllDataForYear.NavigateUrl = "Reports.aspx?rpt=sum"
        End If
        ' End If


    End Sub

    Protected Sub ShowPaymentVoucher()

        Dim cmdParams(1) As System.Data.Common.DbParameter
        cmdParams(0) = DBHelper.CreateParameter("@ID", Data.DbType.Int16, Request.QueryString.Get("vno"))
        If (Request.QueryString.Get("rpt") = "r") Then
            cmdParams(1) = DBHelper.CreateParameter("@IsReciept", Data.DbType.Int16, 1)
        Else
            cmdParams(1) = DBHelper.CreateParameter("@IsReciept", Data.DbType.Int16, 0)
        End If

        Dim dt As System.Data.DataTable = DBHelper.ExecuteTable(Data.CommandType.StoredProcedure, "uspGetVoucherDetail", cmdParams)
        dt.TableName = "VoucherDetails"

        Dim dr As Data.DataRow
        If (dt.Rows.Count < 7) Then
            Dim count As Integer
            For count = 0 To 7 - dt.Rows.Count - 1
                dr = dt.NewRow
                dr("ReceiptCash") = 0
                dr("ReceiptBank") = 0
                dr("PaymentCash") = 0
                dr("PaymentBank") = 0
                dr("PaymentWithdrawn") = 0
                dt.Rows.Add(dr)
            Next


        End If

        Dim total As Decimal = 0
        For Each dr In dt.Rows
            total = total + Decimal.Parse(dr.Item("ReceiptCash").ToString())
            total = total + Decimal.Parse(dr.Item("ReceiptBank").ToString())
            total = total + Decimal.Parse(dr.Item("PaymentCash").ToString())
            total = total + Decimal.Parse(dr.Item("PaymentBank").ToString())
            total = total + Decimal.Parse(dr.Item("PaymentWithdrawn").ToString())
        Next


        Dim path As String = "\vouchers\paymentvoucher.rpt"
        If Request.QueryString("rpt") = "r" Then
            path = "\vouchers\cashvoucher.rpt"
        End If

        Dim rptSP As New ReportDocument
        rptSP.Load(Server.MapPath("~") + path)

        rptSP.SetDataSource(dt)

        Dim paramField2 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal2 As New CrystalDecisions.Shared.ParameterDiscreteValue()

        paramField2.ParameterFieldName = "total"
        discreteVal2.Value = total.ToString()
        paramField2.CurrentValues.Add(discreteVal2)
        Dim paramFields As New CrystalDecisions.Shared.ParameterFields()
        paramFields.Add(paramField2)
        crvAccounts.ParameterFieldInfo = paramFields

        crvAccounts.ReportSource = rptSP

    End Sub

    Protected Sub ShowYearlySummary()

        Dim cashR As Decimal = 0
        Dim bankR As Decimal = 0
        Dim cashP As Decimal = 0
        Dim bankP As Decimal = 0
        Dim bankW As Decimal = 0
        Dim totalSales As Decimal = 0
        Dim totalPurchaces As Decimal = 0
        Dim otherExp As Decimal = 0

        Dim anilI As Decimal = 0
        Dim gnanI As Decimal = 0
        Dim prasadI As Decimal = 0
        Dim praveenI As Decimal = 0
        Dim otherI As Decimal = 0
        Dim totalI As Decimal = 0

        Dim cmdParams(0) As System.Data.Common.DbParameter
        cmdParams(0) = DBHelper.CreateParameter("@IsReciept", Data.DbType.Int16, 0)

        Dim dt1 As System.Data.DataTable = DBHelper.ExecuteTable(Data.CommandType.StoredProcedure, "uspGetAllVouchers", cmdParams)
        dt1.TableName = "VoucherPayments"

        cmdParams(0) = DBHelper.CreateParameter("@IsReciept", Data.DbType.Int16, 1)
        Dim dt2 As System.Data.DataTable = DBHelper.ExecuteTable(Data.CommandType.StoredProcedure, "uspGetAllVouchers", cmdParams)
        dt2.TableName = "VoucherReciepts"

        Dim dtTotal As System.Data.DataTable = dt1.Clone

        Dim dr As Data.DataRow
        For Each dr In dt1.Rows
            'cashR = cashR + Decimal.Parse(dr("ReceiptCash").ToString())
            'bankR = bankR + Decimal.Parse(dr("ReceiptBank").ToString())
            cashP = cashP + Decimal.Parse(dr("PaymentCash").ToString())
            bankP = bankP + Decimal.Parse(dr("PaymentBank").ToString())
            bankW = bankW + Decimal.Parse(dr("PaymentWithdrawn").ToString())

            If (Integer.Parse(dr("TransactionTypeID").ToString()) = 21) Then
                totalPurchaces = totalPurchaces + Decimal.Parse(dr("PaymentCash").ToString())
                totalPurchaces = totalPurchaces + Decimal.Parse(dr("PaymentBank").ToString())
            ElseIf (Integer.Parse(dr("TransactionTypeID").ToString()) = 24) Then
                If (Integer.Parse(dr("FromTo").ToString()) = 3) Then
                    anilI = anilI - Decimal.Parse(dr("PaymentCash").ToString()) - Decimal.Parse(dr("PaymentBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 1) Then
                    gnanI = gnanI - Decimal.Parse(dr("PaymentCash").ToString()) - Decimal.Parse(dr("PaymentBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 4) Then
                    prasadI = prasadI - Decimal.Parse(dr("PaymentCash").ToString()) - Decimal.Parse(dr("PaymentBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 2) Then
                    praveenI = praveenI - Decimal.Parse(dr("PaymentCash").ToString()) - Decimal.Parse(dr("PaymentBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 7) Then
                    otherI = otherI - Decimal.Parse(dr("PaymentCash").ToString()) - Decimal.Parse(dr("PaymentBank").ToString())
                End If
            Else
                If (Integer.Parse(dr("TransactionTypeID").ToString()) > 1) Then
                    otherExp = otherExp + Decimal.Parse(dr("PaymentCash").ToString())
                    otherExp = otherExp + Decimal.Parse(dr("PaymentBank").ToString())
                End If
            End If

            dtTotal.Rows.Add(dr.ItemArray)
        Next
        For Each dr In dt2.Rows
            cashR = cashR + Decimal.Parse(dr("ReceiptCash").ToString())
            bankR = bankR + Decimal.Parse(dr("ReceiptBank").ToString())
            'cashP = cashP + Decimal.Parse(dr("PaymentCash").ToString())
            'bankP = bankP + Decimal.Parse(dr("PaymentBank").ToString())
            'bankW = bankW + Decimal.Parse(dr("PaymentWithdrawn").ToString())

            If (Integer.Parse(dr("TransactionTypeID").ToString()) = 3 Or Integer.Parse(dr("TransactionTypeID").ToString()) = 2) Then
                totalSales = totalSales + Decimal.Parse(dr("ReceiptCash").ToString()) + Decimal.Parse(dr("ReceiptBank").ToString())
            End If

            If (Integer.Parse(dr("TransactionTypeID").ToString()) = 23) Then
                ' anil - 3
                ' gnan - 1
                ' prasad - 4
                ' praveen - 2
                ' Others - 7
                If (Integer.Parse(dr("FromTo").ToString()) = 3) Then
                    anilI = anilI + Decimal.Parse(dr("ReceiptCash").ToString()) + Decimal.Parse(dr("ReceiptBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 1) Then
                    gnanI = gnanI + Decimal.Parse(dr("ReceiptCash").ToString()) + Decimal.Parse(dr("ReceiptBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 4) Then
                    prasadI = prasadI + Decimal.Parse(dr("ReceiptCash").ToString()) + Decimal.Parse(dr("ReceiptBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 2) Then
                    praveenI = praveenI + Decimal.Parse(dr("ReceiptCash").ToString()) + Decimal.Parse(dr("ReceiptBank").ToString())
                ElseIf (Integer.Parse(dr("FromTo").ToString()) = 7) Then
                    otherI = otherI + Decimal.Parse(dr("ReceiptCash").ToString()) + Decimal.Parse(dr("ReceiptBank").ToString())
                End If
            End If

            dtTotal.Rows.Add(dr.ItemArray)
        Next

        totalI = anilI + gnanI + prasadI + praveenI + otherI

        Dim dtSorted As Data.DataTable = dtTotal.Clone

        dtTotal.DefaultView.Sort = " ActualDate ASC "

        Dim r As Data.DataRowView
        For Each r In dtTotal.DefaultView
            dtSorted.Rows.Add(r.Row.ItemArray)
        Next
        'Dim rowList As Data.DataRow() = dtTotal.Select("", "VoucherDate ASC")


        'For Each dr In rowList
        '    dtSorted.Rows.Add(dr.ItemArray)
        'Next


        Dim rptSP As New ReportDocument
        rptSP.Load(Server.MapPath("~") + "\vouchers\Summary.rpt")

        rptSP.SetDataSource(dtSorted)

        Dim paramField1 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal1 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField2 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal2 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField3 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal3 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField4 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal4 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField5 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal5 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField6 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal6 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField7 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal7 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField8 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal8 As New CrystalDecisions.Shared.ParameterDiscreteValue()

        Dim paramField9 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal9 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField10 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal10 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField11 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal11 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField12 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal12 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField13 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal13 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField14 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal14 As New CrystalDecisions.Shared.ParameterDiscreteValue()

        paramField1.ParameterFieldName = "CashR"
        discreteVal1.Value = cashR.ToString()
        paramField2.ParameterFieldName = "BankR"
        discreteVal2.Value = bankR.ToString()
        paramField3.ParameterFieldName = "CashP"
        discreteVal3.Value = cashP.ToString()
        paramField4.ParameterFieldName = "BankP"
        discreteVal4.Value = bankP.ToString()
        paramField5.ParameterFieldName = "BankW"
        discreteVal5.Value = bankW.ToString()
        paramField6.ParameterFieldName = "TotalSales"
        discreteVal6.Value = totalSales.ToString()
        paramField7.ParameterFieldName = "Purchases"
        discreteVal7.Value = totalPurchaces.ToString()
        paramField8.ParameterFieldName = "OtherExpenses"
        discreteVal8.Value = otherExp.ToString()

        paramField9.ParameterFieldName = "AnilI"
        discreteVal9.Value = anilI.ToString()
        paramField10.ParameterFieldName = "GnanI"
        discreteVal10.Value = gnanI.ToString()
        paramField11.ParameterFieldName = "PrasadI"
        discreteVal11.Value = prasadI.ToString()
        paramField12.ParameterFieldName = "PraveenI"
        discreteVal12.Value = praveenI.ToString()
        paramField13.ParameterFieldName = "OtherI"
        discreteVal13.Value = otherI.ToString()
        paramField14.ParameterFieldName = "TotalI"
        discreteVal14.Value = totalI.ToString()

        paramField1.CurrentValues.Add(discreteVal1)
        paramField2.CurrentValues.Add(discreteVal2)
        paramField3.CurrentValues.Add(discreteVal3)
        paramField4.CurrentValues.Add(discreteVal4)
        paramField5.CurrentValues.Add(discreteVal5)
        paramField6.CurrentValues.Add(discreteVal6)
        paramField7.CurrentValues.Add(discreteVal7)
        paramField8.CurrentValues.Add(discreteVal8)
        paramField9.CurrentValues.Add(discreteVal9)
        paramField10.CurrentValues.Add(discreteVal10)
        paramField11.CurrentValues.Add(discreteVal11)
        paramField12.CurrentValues.Add(discreteVal12)
        paramField13.CurrentValues.Add(discreteVal13)
        paramField14.CurrentValues.Add(discreteVal14)

        Dim allParams As New CrystalDecisions.Shared.ParameterFields()
        allParams.Add(paramField1)
        allParams.Add(paramField2)
        allParams.Add(paramField3)
        allParams.Add(paramField4)
        allParams.Add(paramField5)
        allParams.Add(paramField6)
        allParams.Add(paramField7)
        allParams.Add(paramField8)
        allParams.Add(paramField9)
        allParams.Add(paramField10)
        allParams.Add(paramField11)
        allParams.Add(paramField12)
        allParams.Add(paramField13)
        allParams.Add(paramField14)
        crvAccounts.ParameterFieldInfo = allParams

        crvAccounts.ReportSource = rptSP
        'rptSP.Close()
        'rptSP.Dispose()
    End Sub

    Protected Sub ShowMonthlySummary()

        Dim dtFrom As Date = Date.Parse(Request.QueryString("from"))
        Dim dtTo As Date = Date.Parse(Request.QueryString("to"))

        Dim cashR As Decimal = 0
        Dim bankR As Decimal = 0
        Dim cashP As Decimal = 0
        Dim bankP As Decimal = 0
        Dim bankW As Decimal = 0
        Dim totalSales As Decimal = 0
        Dim totalPurchaces As Decimal = 0
        Dim otherExp As Decimal = 0

        Dim cmdParams(0) As System.Data.Common.DbParameter
        cmdParams(0) = DBHelper.CreateParameter("@IsReciept", Data.DbType.Int16, 0)

        Dim dt1 As System.Data.DataTable = DBHelper.ExecuteTable(Data.CommandType.StoredProcedure, "uspGetAllVouchers", cmdParams)
        dt1.TableName = "VoucherPayments"

        cmdParams(0) = DBHelper.CreateParameter("@IsReciept", Data.DbType.Int16, 1)
        Dim dt2 As System.Data.DataTable = DBHelper.ExecuteTable(Data.CommandType.StoredProcedure, "uspGetAllVouchers", cmdParams)
        dt2.TableName = "VoucherReciepts"

        Dim dtTotal As System.Data.DataTable = dt1.Clone

        Dim dr As Data.DataRow
        For Each dr In dt1.Rows
            If (Date.Parse(dr("VoucherDate").ToString()) >= dtFrom And Date.Parse(dr("VoucherDate").ToString()) <= dtTo) Then

                'cashR = cashR + Decimal.Parse(dr("ReceiptCash").ToString())
                'bankR = bankR + Decimal.Parse(dr("ReceiptBank").ToString())
                cashP = cashP + Decimal.Parse(dr("PaymentCash").ToString())
                bankP = bankP + Decimal.Parse(dr("PaymentBank").ToString())
                bankW = bankW + Decimal.Parse(dr("PaymentWithdrawn").ToString())

                If (Integer.Parse(dr("TransactionTypeID").ToString()) = 21) Then
                    totalPurchaces = totalPurchaces + Decimal.Parse(dr("PaymentCash").ToString())
                    totalPurchaces = totalPurchaces + Decimal.Parse(dr("PaymentBank").ToString())
                Else
                    If (Integer.Parse(dr("TransactionTypeID").ToString()) > 1) Then
                        otherExp = otherExp + Decimal.Parse(dr("PaymentCash").ToString())
                        otherExp = otherExp + Decimal.Parse(dr("PaymentBank").ToString())
                    End If
                End If

                dtTotal.Rows.Add(dr.ItemArray)
            End If

        Next
        For Each dr In dt2.Rows
            If (Date.Parse(dr("VoucherDate").ToString()) >= dtFrom And Date.Parse(dr("VoucherDate").ToString()) <= dtTo) Then

                cashR = cashR + Decimal.Parse(dr("ReceiptCash").ToString())
                bankR = bankR + Decimal.Parse(dr("ReceiptBank").ToString())
                'cashP = cashP + Decimal.Parse(dr("PaymentCash").ToString())
                'bankP = bankP + Decimal.Parse(dr("PaymentBank").ToString())
                'bankW = bankW + Decimal.Parse(dr("PaymentWithdrawn").ToString())

                If (Integer.Parse(dr("TransactionTypeID").ToString()) = 3 Or Integer.Parse(dr("TransactionTypeID").ToString()) = 2) Then
                    totalSales = totalSales + Decimal.Parse(dr("ReceiptCash").ToString())
                    totalSales = totalSales + Decimal.Parse(dr("ReceiptBank").ToString())
                End If

                dtTotal.Rows.Add(dr.ItemArray)
            End If

        Next

        Dim dv As New Data.DataView(dtTotal)
        dv.Sort = "VoucherDate ASC"

        'Dim dtSorted As Data.DataTable = dtTotal.Clone
        'dtSorted = dv.Table.Copy

        Dim rptSP As New ReportDocument
        rptSP.Load(Server.MapPath("~") + "\vouchers\Summary.rpt")

        rptSP.SetDataSource(dv.ToTable())

        Dim paramField1 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal1 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField2 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal2 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField3 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal3 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField4 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal4 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField5 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal5 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField6 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal6 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField7 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal7 As New CrystalDecisions.Shared.ParameterDiscreteValue()
        Dim paramField8 As New CrystalDecisions.Shared.ParameterField()
        Dim discreteVal8 As New CrystalDecisions.Shared.ParameterDiscreteValue()

        paramField1.ParameterFieldName = "CashR"
        discreteVal1.Value = cashR.ToString()
        paramField2.ParameterFieldName = "BankR"
        discreteVal2.Value = bankR.ToString()
        paramField3.ParameterFieldName = "CashP"
        discreteVal3.Value = cashP.ToString()
        paramField4.ParameterFieldName = "BankP"
        discreteVal4.Value = bankP.ToString()
        paramField5.ParameterFieldName = "BankW"
        discreteVal5.Value = bankW.ToString()
        paramField6.ParameterFieldName = "TotalSales"
        discreteVal6.Value = totalSales.ToString()
        paramField7.ParameterFieldName = "Purchases"
        discreteVal7.Value = totalPurchaces.ToString()
        paramField8.ParameterFieldName = "OtherExpenses"
        discreteVal8.Value = otherExp.ToString()

        paramField1.CurrentValues.Add(discreteVal1)
        paramField2.CurrentValues.Add(discreteVal2)
        paramField3.CurrentValues.Add(discreteVal3)
        paramField4.CurrentValues.Add(discreteVal4)
        paramField5.CurrentValues.Add(discreteVal5)
        paramField6.CurrentValues.Add(discreteVal6)
        paramField7.CurrentValues.Add(discreteVal7)
        paramField8.CurrentValues.Add(discreteVal8)

        Dim allParams As New CrystalDecisions.Shared.ParameterFields()
        allParams.Add(paramField1)
        allParams.Add(paramField2)
        allParams.Add(paramField3)
        allParams.Add(paramField4)
        allParams.Add(paramField5)
        allParams.Add(paramField6)
        allParams.Add(paramField7)
        allParams.Add(paramField8)
        crvAccounts.ParameterFieldInfo = allParams

        crvAccounts.ReportSource = rptSP
    End Sub

    Protected Sub lnkMonthlySummary_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkMonthlySummary.Click
        Response.Redirect("Reports.aspx?rpt=mon&from=" + txtFrom.Text + "&to=" + txtTo.Text, True)
    End Sub
End Class
